﻿using _1142063_1142080_1142098.Models.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace _1142063_1142080_1142098.Models.Services
{
    public class PhieuDangKyTourService
    {
        public static string DangKyTour(PhieuDangKyTour phieuDangKyTour, List<ChiTietPhieuDangKyTour> chiTietPhieuDangKyTourList, byte isTrongNuoc)
        {
            string error = string.Empty;
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var transaction = myDataProvider.sqlConnection.BeginTransaction(IsolationLevel.RepeatableRead);
            try
            {
                var parameterList = new List<SqlParameter>();
                parameterList.Add(new SqlParameter("@IdTour", SqlDbType.DateTime));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.IdTour;
                int soChoConNhan = 0;
                if (isTrongNuoc == (byte)1)
                {
                    soChoConNhan = (int)myDataProvider.ExcuteScalarWithParameters("spu_tourDuLichTrongNuoc_laySoChoConNhan", parameterList, transaction);
                }
                else
                {
                    soChoConNhan = (int)myDataProvider.ExcuteScalarWithParameters("spu_tourDuLichTrongNuoc_laySoChoConNhan", parameterList, transaction);
                }
                if(soChoConNhan<phieuDangKyTour.TongSoKhach)
                {
                    transaction.Rollback();
                    myDataProvider.Close();
                    return "Số chỗ còn nhận không đủ";
                }
                //Phieu dang ky
                parameterList=new List<SqlParameter>();
                parameterList.Add(new SqlParameter("@Id", SqlDbType.DateTime));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.Id;
                parameterList.Add(new SqlParameter("@MaPhieu", SqlDbType.UniqueIdentifier));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.MaPhieu;
                parameterList.Add(new SqlParameter("@TongSoKhach", SqlDbType.Int));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.TongSoKhach;
                parameterList.Add(new SqlParameter("@SoNguoiLon", SqlDbType.Int));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.SoNguoiLon;
                parameterList.Add(new SqlParameter("@SoTreEm", SqlDbType.Int));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.SoTreEm??0;
                parameterList.Add(new SqlParameter("@SoTreNho", SqlDbType.Int));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.SoTreNho??0;
                parameterList.Add(new SqlParameter("@TongTien", SqlDbType.Decimal));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.TongTien;
                parameterList.Add(new SqlParameter("@IdTour", SqlDbType.DateTime));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.IdTour;
                parameterList.Add(new SqlParameter("@HoTenNguoiDangKy", SqlDbType.NVarChar));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.HoTenNguoiDangKy;
                parameterList.Add(new SqlParameter("@DiaChi", SqlDbType.NVarChar));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.DiaChi??string.Empty;
                parameterList.Add(new SqlParameter("@DienThoai", SqlDbType.NVarChar));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.DienThoai;
                parameterList.Add(new SqlParameter("@Email", SqlDbType.NVarChar));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.Email;
                parameterList.Add(new SqlParameter("@GhiChu", SqlDbType.NVarChar));
                parameterList[parameterList.Count - 1].Value = phieuDangKyTour.GhiChu ?? string.Empty;
                if (isTrongNuoc == (byte)1)
                {
                    myDataProvider.ExcuteQueryWithParameters("spu_phieuDangKyTourTrongNuoc_them", parameterList, transaction);
                }
                else
                {
                    myDataProvider.ExcuteQueryWithParameters("spu_phieuDangKyTourNuocNgoai_them", parameterList, transaction);
                }
                //Chi tiet dang ky tour
                foreach (var chiTietPhieuDangKyTour in chiTietPhieuDangKyTourList)
                {
                    parameterList = new List<SqlParameter>();
                    parameterList.Add(new SqlParameter("@Id", SqlDbType.DateTime));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.Id;
                    parameterList.Add(new SqlParameter("@IdPhieuDangKy", SqlDbType.DateTime));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.IdPhieuDangKyTour;
                    parameterList.Add(new SqlParameter("@HoTenKhachHang", SqlDbType.NVarChar));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.HoTenKhachHang;
                    parameterList.Add(new SqlParameter("@NgaySinh", SqlDbType.DateTime));
                    if (chiTietPhieuDangKyTour.NgaySinh != null)
                    {
                        parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.NgaySinh;
                    }
                    else
                    {
                        parameterList[parameterList.Count - 1].Value = DBNull.Value;
                    }
                    parameterList.Add(new SqlParameter("@GioiTinh", SqlDbType.TinyInt));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.GioiTinh;
                    parameterList.Add(new SqlParameter("@DiaChi", SqlDbType.NVarChar));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.DiaChi ?? string.Empty;
                    parameterList.Add(new SqlParameter("@LoaiKhach", SqlDbType.TinyInt));
                    if (chiTietPhieuDangKyTour.LoaiKhach != null)
                    {
                        parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.LoaiKhach;
                    }
                    else
                    {
                        parameterList[parameterList.Count - 1].Value = DBNull.Value;
                    }
                    parameterList.Add(new SqlParameter("@DoTuoi", SqlDbType.TinyInt));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.DoTuoi;
                    parameterList.Add(new SqlParameter("@QuocTich", SqlDbType.NVarChar));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.QuocTich ?? string.Empty;
                    parameterList.Add(new SqlParameter("@SoPassport", SqlDbType.NVarChar));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.SoPassport ?? string.Empty;
                    parameterList.Add(new SqlParameter("@NgayCap", SqlDbType.DateTime));
                    if (chiTietPhieuDangKyTour.NgayCap != null)
                    {
                        parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.NgayCap;
                    }
                    else
                    {
                        parameterList[parameterList.Count - 1].Value = DBNull.Value;
                    }
                    parameterList.Add(new SqlParameter("@NgayHetHan", SqlDbType.DateTime));
                    if (chiTietPhieuDangKyTour.NgayHetHan != null)
                    {
                        parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.NgayHetHan;
                    }
                    else
                    {
                        parameterList[parameterList.Count - 1].Value = DBNull.Value;
                    }
                    parameterList.Add(new SqlParameter("@PhongDon", SqlDbType.TinyInt));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.PhongDon;
                    parameterList.Add(new SqlParameter("@PhuThuVisaTaiNhapVN", SqlDbType.TinyInt));
                    if (chiTietPhieuDangKyTour.PhuThuVisaTaiNhapVN != null)
                    {
                        parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.PhuThuVisaTaiNhapVN;
                    }
                    else
                    {
                        parameterList[parameterList.Count - 1].Value = DBNull.Value;
                    }
                    parameterList.Add(new SqlParameter("@Gia", SqlDbType.Decimal));
                    parameterList[parameterList.Count - 1].Value = chiTietPhieuDangKyTour.Gia;
                    if (isTrongNuoc == (byte)1)
                    {
                        myDataProvider.ExcuteQueryWithParameters("spu_chiTietPhieuDangKyTourTrongNuoc_them", parameterList, transaction);
                    }
                    else
                    {
                        myDataProvider.ExcuteQueryWithParameters("spu_chiTietPhieuDangKyTourNuocNgoai_them", parameterList, transaction);
                    }
                    parameterList=new List<SqlParameter>();
                    parameterList.Add(new SqlParameter("@SoChoConNhan", SqlDbType.Int));
                    parameterList[parameterList.Count - 1].Value = soChoConNhan-phieuDangKyTour.TongSoKhach;
                    parameterList.Add(new SqlParameter("@Id", SqlDbType.DateTime));
                    parameterList[parameterList.Count - 1].Value = phieuDangKyTour.IdTour;
                    if (isTrongNuoc == (byte)1)
                    {
                        myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichTrongNuoc_capNhatSoLuongConNhanTheoId", parameterList, transaction);
                    }
                    else
                    {
                        myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichNuocNgoai_capNhatSoLuongConNhanTheoId", parameterList, transaction);
                    }
                }
                transaction.Commit();
                myDataProvider.Close();
            }
            catch (Exception e)
            {
                error = e.Message;
                transaction.Rollback();
                myDataProvider.Close();
            }
            return error;
        }
    }
}